package com.tentact.dao;

import com.tentact.vo.Employee;
import com.tentact.vo.Meeting;
import com.tentact.vo.MeetingParticipants;
import com.tentact.vo.MeetingRoom;

import java.sql.SQLException;
import java.util.List;

// 会议预定
public class MeetingDao {
    // 添加会议
    public int insertMeeting(MeetingRoom meetingRoom) {
        String sql = "INSERT into meetingroom(roomcode,roomname,roomcapacity,roomstatus,description)VALUE(?,?,?,?,?)";
        Object[] params = {meetingRoom.getRoomcode(),meetingRoom.getRoomname(),meetingRoom.getRoomcapacity(),meetingRoom.getRoomstatus(),meetingRoom.getDescription()};
        return Dbutil.executeUpdate(sql,params);
    }


    //通过会议名称查询会议名称，会议室，起始时间，结束时间,预定时间
    public List<Meeting> SelelctByNameMeetingSeven(int employeeid) {
        String sql = "select distinct m.meetingstatus ,m.meetingname,mr.roomname,m.begintime,m.endtime,m.numofparticipants,m.description,m.reservationtime , e.employeename,m.meetingstatus   from employee e,meetingparticipants me,meeting m , meetingroom mr   where e.employeeid=me.participantid and m.reservationistid=e.employeeid and  m.roomid=mr.roomid  and e.employeeid=? ";
        Object[] params = {employeeid};
        RowMapper<Meeting> rm = (rs) -> {
            Meeting meeting = new Meeting();
            MeetingRoom meetingRoom = new MeetingRoom();
            Employee employee=new Employee();
            try {
                meeting.setMeetingstatus(rs.getInt("meetingstatus"));
                meeting.setMeetingname(rs.getString("meetingname"));
                meetingRoom.setRoomname(rs.getString("roomname"));
                meeting.setBegintime(rs.getString("begintime"));
                meeting.setEndtime(rs.getString("endtime"));
                meeting.setNumofparticipants(rs.getInt("numofparticipants"));
                meeting.setDescription(rs.getString("description"));
                meeting.setReservationtime(rs.getString("reservationtime"));
                employee.setEmployeename(rs.getString("employeename"));
                meeting.setMeetingstatus(rs.getInt("meetingstatus"));
                meeting.setMeetingRoom(meetingRoom);
                meeting.setEmployee(employee);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return meeting;
        };
        return Dbutil.executeQuery(sql, params, rm);
    }

    public List<Meeting> SelelctByNameMeetingSevens(String meetingname) {
        String sql = "select m.meetingname,mr.roomname,m.begintime,m.endtime,m.numofparticipants,m.description,m.meetingstatus  from meeting m join meetingroom mr on m.roomid=mr.roomid where  m.meetingname like ?";
        Object[] params = {"%" + meetingname + "%"};
        RowMapper<Meeting> rm = (rs) -> {
            Meeting meeting = new Meeting();
            MeetingRoom meetingRoom = new MeetingRoom();
            Employee employee=new Employee();
            try {
                meeting.setMeetingname(rs.getString("meetingname"));
                meetingRoom.setRoomname(rs.getString("roomname"));
                meeting.setBegintime(rs.getString("begintime"));
                meeting.setEndtime(rs.getString("endtime"));
                meeting.setNumofparticipants(rs.getInt("numofparticipants"));
                meeting.setDescription(rs.getString("description"));

                meeting.setMeetingstatus(rs.getInt("meetingstatus"));
                meeting.setMeetingRoom(meetingRoom);
                meeting.setEmployee(employee);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return meeting;
        };
        return Dbutil.executeQuery(sql, params, rm);
    }
    //通过会议名称查询会议名称，会议室，起始时间，结束时间 未来七天
    public List<Meeting> SelectByNameMeeting(String meetingname) {
        String sql = "select m.meetingname,mr.roomname,m.begintime,m.endtime,m.numofparticipants,m.description  from meeting m join meetingroom mr on m.roomid=mr.roomid where  BeginTime BETWEEN DATE_SUB(NOW(),INTERVAL 0 DAY) AND DATE_SUB(NOW(),INTERVAL -7 DAY) and m.meetingname like ?";
        Object[] params = {"%" + meetingname + "%"};
        RowMapper<Meeting> rm = (rs) -> {
            Meeting meeting = new Meeting();
            MeetingRoom meetingRoom = new MeetingRoom();
            try {
                meeting.setMeetingname(rs.getString("meetingname"));
                meetingRoom.setRoomname(rs.getString("roomname"));
                meeting.setBegintime(rs.getString("begintime"));
                meeting.setEndtime(rs.getString("endtime"));
                meeting.setNumofparticipants(rs.getInt("numofparticipants"));
                meeting.setDescription(rs.getString("description"));
                meeting.setMeetingRoom(meetingRoom);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return meeting;
        };
        return Dbutil.executeQuery(sql, params, rm);
    }

    //通过会议名称查询
    public List<Meeting> SelelctByNameMeetingMan(int employeeid ) {
        String sql = "SELECT m.numofparticipants,m.description, m.MeetingName,mr.roomName,m.begintime,m.endtime,m.reservationtime,e.employeename FROM meeting m JOIN meetingroom mr ON m.roomId = mr.roomId JOIN employee e ON m.reservationistid = e.employeeid JOIN meetingparticipants mp ON m.meetingid = mp.meetingid where mp.participantid =? ";
        Object[] params = {employeeid};
        RowMapper<Meeting> rm = (rs) -> {
            Meeting meeting = new Meeting();
            MeetingRoom meetingRoom = new MeetingRoom();
            Employee employee = new Employee();
            try {
                meeting.setNumofparticipants(rs.getInt("numofparticipants"));
                meeting.setDescription(rs.getString("description"));
                meeting.setMeetingname(rs.getString("meetingname"));
                meetingRoom.setRoomname(rs.getString("roomname"));
                meeting.setBegintime(rs.getString("begintime"));
                meeting.setEndtime(rs.getString("endtime"));
                meeting.setReservationtime(rs.getString("reservationtime"));
                employee.setEmployeename(rs.getString("employeename"));
                meeting.setMeetingRoom(meetingRoom);
                meeting.setEmployee(employee);

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return meeting;
        };
        return Dbutil.executeQuery(sql, params, rm);
    }

    //根据会议名称
    public Meeting SelectMeetingNameSesson(String meetingname) {
        String sql = "SELECT MeetingName,NumOfParticipants,BeginTime,EndTime,Description,ReservationTime  from meeting where MeetingName=?";
        Object[] paras = {meetingname};
        RowMapper<Meeting> rm = (rs) -> {
            Meeting meeting = new Meeting();
            try {
                meeting.setMeetingname(rs.getString("meetingname"));
                meeting.setNumofparticipants(rs.getInt("numofparticipants"));
                meeting.setBegintime(rs.getString("begintime"));
                meeting.setEndtime(rs.getString("endtime"));
                meeting.setDescription(rs.getString("description"));
                meeting.setReservationtime(rs.getString("reservationtime"));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return meeting;
        };
        return Dbutil.executeQuerySingle(sql, paras, rm);
    }
    //通过会议姓名查询员工数据
    public List<Meeting> SelectByNameMettingThree(String meetingname){
        String sql="select e.employeename,e.phone,e.email,e.employeeid,m.participantid,me.meetingid,m.meetingid,me.meetingname from meetingparticipants m ,meeting me,employee e where e.employeeid =m.participantid and me.meetingid =m.meetingid and me.meetingname =?";
        Object[] params = {meetingname};
        RowMapper<Meeting> rm = (rs) -> {
            Meeting meeting = new Meeting();
            MeetingParticipants meetingParticipants=new MeetingParticipants();
            Employee employee = new Employee();
            try {
                employee.setEmployeename(rs.getString("employeename"));
                employee.setPhone(rs.getString("phone"));
                employee.setEmail(rs.getString("email"));
                employee.setEmployeeid(rs.getInt("employeeid"));
                meetingParticipants.setParticipantid(rs.getInt("participantid"));
                meeting.setMeetingid(rs.getInt("meetingid"));
                meetingParticipants.setMeetingid(rs.getInt("meetingid"));
                meeting.setMeetingname(rs.getString("meetingname"));
                meeting.setEmployee(employee);
                meeting.setMeetingParticipants(meetingParticipants);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return meeting;
        };
        return Dbutil.executeQuery(sql, params, rm);
    }
    //修改会议状态
    public int updateByMeetings(String meetingname) {
        String sql = "update Meeting set meetingstatus = 2 where meetingname =?";
        Object[] params = {meetingname};
        return Dbutil.executeUpdate(sql, params);
    }

    //根据id查询会议室
    public MeetingRoom selectByRoomid(int roomid) {
        String sql = "SELECT roomid,roomcode,roomname,roomcapacity,roomstatus,description from meetingroom WHERE RoomId = ?";
        Object[] params = {roomid};
        RowMapper<MeetingRoom> rm = (rs) -> {
            MeetingRoom meetingRoom = new MeetingRoom();
            try {
                meetingRoom.setRoomid(rs.getInt("roomid"));
                meetingRoom.setRoomcode(rs.getString("roomcode"));
                meetingRoom.setRoomname(rs.getString("roomname"));
                meetingRoom.setRoomcapacity(rs.getInt("roomcapacity"));
                meetingRoom.setRoomstatus(rs.getInt("roomstatus"));
                meetingRoom.setDescription(rs.getString("description"));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return meetingRoom;
        };
        return Dbutil.executeQuerySingle(sql,params,rm);
    }
    //根据名称模糊查询
    public List<MeetingRoom> selectByroomname() {
        String sql = "SELECT roomid,roomcode,roomname,roomcapacity,roomstatus,description from meetingroom";

        RowMapper<MeetingRoom> rm = (rs) -> {
            MeetingRoom meetingRoom = new MeetingRoom();
            try {
                meetingRoom.setRoomid(rs.getInt("roomid"));
                meetingRoom.setRoomcode(rs.getString("roomcode"));
                meetingRoom.setRoomname(rs.getString("roomname"));
                meetingRoom.setRoomcapacity(rs.getInt("roomcapacity"));
                meetingRoom.setRoomstatus(rs.getInt("roomstatus"));
                meetingRoom.setDescription(rs.getString("description"));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return meetingRoom;
        };
        return Dbutil.executeQuery(sql,null,rm);
    }
    //根据id更新会议室状态
    public int updateMeetroom(MeetingRoom meetingRoom) {
        String sql = "UPDATE meetingroom set roomcode=?,roomname=?,roomcapacity=?,roomstatus=?,description=? WHERE RoomId = ?";
        Object[] params = {meetingRoom.getRoomcode(),meetingRoom.getRoomname(),meetingRoom.getRoomcapacity(),meetingRoom.getRoomstatus(),meetingRoom.getDescription(),meetingRoom.getRoomid()};
        return Dbutil.executeUpdate(sql,params);
    }

}

